package kr.hkit.powerjava.chapter26.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;






import kr.hkit.powerjava.chapter26.common.ConnectionFactory;
import kr.hkit.powerjava.chapter26.common.DbUtil;
import kr.hkit.powerjava.chapter26.dto.Department;
//인터페이스에서 구현받아 그 기능을 
//오버라이드 하고 그 메서드에 각클래스의 기능을 코딩한다
public class DepartmentDAO implements DaoInterface<Department>{
	//DB와의 연동을 위한 선언
	private Connection connection;//디비와의 연동
	private PreparedStatement pstmt;//쿼리문을 날려주는 기능
	//생성자만들기
	public DepartmentDAO(Connection connection) {
		super();
		this.connection = connection;
	}
	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	//employee에 해당되는 기능들 넣기
	@Override
	public void insertItem(Department item) throws SQLException {
		String sql="insert into department values(?,?,?)";//인자를 넣어준다
		//connection=ConnectionFactory.getConnection();
		//위의문장을 준비시킨다(쿼리날리기)
		pstmt=connection.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptno());
		pstmt.setString(2, item.getDeptname());
		pstmt.setInt(3, item.getFloor());
		//위의 값들을 업데이트하겠다
		pstmt.executeUpdate();
		//객체를 사용한뒤에는 반드시 닫아준다
		DbUtil.close(pstmt);
		
	}

	@Override
	public void deleteItemByNo(Department item) throws SQLException {
		String sql="delete from department where deptno=?";
		//connection=ConnectionFactory.getConnection();
		pstmt=connection.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptno());
		//System.out.println(pstmt);
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}

	@Override
	public void updateItemByNo(Department item) throws SQLException {
		String sql="update department set deptname=?, floor=? where deptno=?";
		//connection=ConnectionFactory.getConnection();
		pstmt=connection.prepareStatement(sql);
		pstmt.setString(1, item.getDeptname());
		pstmt.setInt(2, item.getFloor());
		pstmt.setInt(3, item.getDeptno());
		//System.out.println(pstmt);
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}
	//출력시 차례대로 보여주기위함인것이다
	@Override
	public ArrayList<Department> listItems() throws SQLException {
		ArrayList<Department> list= new ArrayList<>();
		String sql="select deptno,deptname,floor from department";
		//connection=ConnectionFactory.getConnection();
		pstmt=connection.prepareStatement(sql);
		//select는 ResultSet으로 받는다 (select는 값을 받아오는것이기 때문에)
		//excuteQuery는 select일때 사용
		ResultSet rs= pstmt.executeQuery();
		//차례대로의 출력을 위해 while문 돌려준다
		while(rs.next()){//다음 레코드가 있는가??
			//이미 있는 값들을 가져오는것이기(db에 있는) 때문에
			list.add(new Department(
					rs.getInt("deptno"), 
					rs.getString("deptname"), 
					rs.getInt("floor")));
			
		}
		DbUtil.close(rs, pstmt, connection);
		return list;
	}
	//검색
	@Override
	public Department searchItem(Department item) throws SQLException {
		Department department=null;
		String sql="select deptno,deptname,floor from department where deptno=?";
		pstmt=connection.prepareStatement(sql);
		ResultSet rs= pstmt.executeQuery();
		if(rs.next()) {
		department.setDeptno(rs.getInt(1));
		department.setDeptname(rs.getString(2));
		department.setFloor(rs.getInt(3));
		}
		return department;
	}
	
}
